################
#PSRM: Explaining Support for Redistribution: Social Insurance Systems and Fairness
#
#Observational Data
#Part VIII: Social Spending and Top Tax
#
#Verena Fetscher
#July 2022
####################

rm(list=ls())


##########################
#Load Data
##########################

# Social expenditure
# In percentage of total government expenditure
# Source:https://stats.oecd.org/Index.aspx?DataSetCode=SOCX_DET
socspend<-read.csv("SocialSpending_SOCX_AGG_22102021161051842.csv", header = TRUE, sep = ",", quote = "\"",
                     dec = ".")
names(socspend) <- c("SOURCE","Source","BRANCH",
                     "Branch","TYPEXP","Type.of.Expenditure",
                     "TYPROG","Type.of.Programme","UNIT",
                     "Measure","COUNTRY","Country",
                     "YEAR" ,"Year","Unit.Code",
                     "Unit","PowerCode.Code","PowerCode",
                     "Reference.Period.Code", "Reference.Period","Value",
                     "Flag.Codes","Flags" )

# top statutory tax rate
# Source: https://stats.oecd.org/index.aspx?DataSetCode=TABLE_I7
toptax<-read.csv("TopTax_TABLE_I7_22102021162649482.csv", header = TRUE, sep = ",", quote = "\"",
                   dec = ".")
names(toptax) <- c("COU","Country","TAX",
                   "Income.Tax","YEA","Year",
                   "Unit.Code","Unit","PowerCode.Code",
                   "PowerCode","Reference.Period.Code","Reference.Period",
                   "Value","Flag.Codes","Flags")

# fiscal redistribution by Mahler and Jesuit (2006)
# https://www.lisdatacenter.org/resources/other-databases/?highlight=fiscal%20redistribution
fiscred<-read.csv("fiscal-redistribution-data-2.csv", header = TRUE, sep = ";", quote = "\"",
                 dec = ",")
names(fiscred) <- c("Table.A1..Aspects.of.fiscal.redistribution","X","X.1",
                    "X.2","X.3","X.4","X.5","X.6","X.7","X.8","X.9","X.10" )

# clean
fiscred<-fiscred[-c(1:2,4),]

# First row as colnames
colnames(fiscred)<-NULL
colnames(fiscred)<-as.character(t(fiscred[1,]))

fiscred<-fiscred[-c(1),-(c(3:7,10:25))]

colnames(fiscred)<- c("country","year","red_taxes","red_transfers")

fiscred$red_taxes<-gsub(",", ".", fiscred$red_taxes, fixed = TRUE)
fiscred$red_taxes <- suppressWarnings(sapply(fiscred$red_taxes, as.numeric))

fiscred$red_transfers<-gsub(",", ".", fiscred$red_transfers, fixed = TRUE)
fiscred$red_transfers <- suppressWarnings(sapply(fiscred$red_transfers, as.numeric))

fiscred$year<-as.numeric(fiscred$year)
unique(fiscred$country)

fiscred$country[fiscred$country=="UK"]<-"United Kingdom"
unique(fiscred$country)


# data frame
load("DataFile_08_our.Rda")
data_sub$country<-as.character(data_sub$country)

unique(data_sub$country)

(unique(data_sub$country) %in% unique(fiscred$country))
(unique(data_sub$year) %in% unique(fiscred$year))
##########################
# Filter data
##########################
socspend%>%
  filter(Source=="Public"&
           Branch=="Total"&
           Type.of.Expenditure=="Total"&
           Type.of.Programme=="Total"&
           Measure=="In percentage of Total General Government Expenditure")%>%
  select(Country,Year,Value) -> socspend

names(socspend) <- c("country","year","social_spending")

toptax %>%
  select(Country,Year,Value) -> toptax

names(toptax) <- c("country","year","toptax")

##########################
# Merge data
##########################
data_sub <- merge(data_sub, socspend, by.x = c("country","year"), 
               by.y = c("country","year"), all.x = TRUE, all.y = FALSE)

data_sub <- merge(data_sub, toptax, by.x = c("country","year"), 
                  by.y = c("country","year"), all.x = TRUE, all.y = FALSE)

data_sub <- merge(data_sub, fiscred, by.x = c("country","year"), 
                  by.y = c("country","year"), all.x = TRUE, all.y = FALSE)


  
##########################
#Save data
##########################

#Combine ESS with benefit concentration indicator
save(data_sub,file="DataFile_09_spending_tax.Rda")

##########################
